package mysql

import (
	"bytes"
	"fmt"
	"github.com/astaxie/beego/orm"
	"log"
	"time"
)

type PassengerFlow struct {
	Id         int       `orm:"Column(ID)"`
	ZoneId     int       `orm:"Column(ZoneID)"`
	DateTime   string    `orm:"Column(DateTime)"`
	Time       time.Time `orm:"Column(Time)"`
	Date       string    `orm:"Column(Date)"`
	DateHour   int       `orm:"Column(DateHour)"`
	DateMin    int       `orm:"Column(DateMin)"`
	CountEnter int       `orm:"Column(CountEnter)"`
	CountExit  int       `orm:"Column(CountExit)"`
}

func (p *PassengerFlow) TableName() string {
	return PassegnerTBName()
}

func SelectPassengerflowByDate(date string) (*[]PassengerFlow, error) {
	o := orm.NewOrm()
	pf := new([]PassengerFlow)
	sql := `SELECT ZoneID,DateTime AS Time ,SUM(CountEnter) AS CountEnter,SUM(CountExit) AS CountExit  FROM passengerflow WHERE Date = ? GROUP BY ZoneId,Date,DateHour,DateMin`
	_, err := o.Raw(sql, date).QueryRows(pf)
	return pf, err
}

func SelectPack5PassengerflowByDate(date string) (*[]PassengerFlow, error) {
	o := orm.NewOrm()
	pf := new([]PassengerFlow)
	sql := `SELECT ZoneID,Date,DateHour,DateMin5 AS DateMin ,SUM(CountEnter)  AS CountEnter,SUM(CountExit) AS CountExit FROM T_Pack5_Passengerflow WHERE Date =? GROUP BY ZoneID,Date,DateHour,DateMin5
			UNION
            SELECT ZoneID,Date,DateHour,DateMin5 AS DateMin ,SUM(CountEnter)  AS CountEnter,SUM(CountExit) AS CountExit FROM MT_Pack5_Passengerflow WHERE Date =? GROUP BY ZoneID,Date,DateHour,DateMin5`
	_, err := o.Raw(sql, date, date).QueryRows(pf)
	return pf, err
}

func SelectPassengerflowBusinessByDate(date string) (*[]PassengerFlow, error) {
	o := orm.NewOrm()
	pf := new([]PassengerFlow)
	_, err := o.Raw("SELECT  ID,ZoneID,DateTime AS Time ,CountEnter,CountExit FROM passengerflow_business WHERE Date = ?", date).QueryRows(pf)
	return pf, err
}

func SelectPack5ByDate(date string) (*[]PassengerFlow, error) {
	o := orm.NewOrm()
	pf := new([]PassengerFlow)
	_, err := o.Raw("SELECT ID,ZoneID,Date,DateHour,DateMin5 As DateMin,CountEnter,CountExit FROM T_Pack5_Passengerflow WHERE Date =? "+
		" UNION "+
		" SELECT ID,ZoneID,Date,DateHour,DateMin5  As DateMin,CountEnter,CountExit FROM MT_Pack5_Passengerflow WHERE Date = ?", date, date).QueryRows(pf)
	return pf, err
}

func InsertPassengerFlow(pf *PassengerFlow) (int64, error) {
	o := orm.NewOrm()
	return o.InsertOrUpdate(pf)
}
func DeleteFlow(date string) int64 {
	o := orm.NewOrm()
	res, err := o.Raw("DELETE FROM passengerflow WHERE Date = ?;", date).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		return num
	} else {
		panic(err)
	}

}

func BatchInsert(pfs []PassengerFlow) int64 {
	var buffer bytes.Buffer
	buffer.WriteString("INSERT INTO passengerflow( `ZoneID`, `DateTime`, `Date`, `DateHour`, `DateMin`, `CountEnter`, `CountExit`) VALUES ")
	for _, value := range pfs {
		sql := fmt.Sprintf("(%v,'%s','%s',%v,%v,%v,%v)", value.ZoneId, value.DateTime, value.Date, value.DateHour, value.DateMin, value.CountEnter, value.CountExit)
		buffer.WriteString(sql)
	}
	buffer.WriteString(" ON DUPLICATE KEY UPDATE ZoneID=VALUES(ZoneID),DateTime=VALUES(DateTime),Date=VALUES(Date) ,DateHour=VALUES(DateHour) ,DateMin=VALUES(DateMin) ,CountEnter=VALUES(CountEnter) ,CountExit=VALUES(CountExit);")
	o := orm.NewOrm()
	res, err := o.Raw(buffer.String()).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		return num
	} else {
		log.Println(err)
		return -1
	}
}

func BatchInsert2(pfs []PassengerFlow) int {
	o := orm.NewOrm()
	p, err := o.Raw("INSERT INTO passengerflow( `ZoneID`, `DateTime`, `Date`, `DateHour`, `DateMin`, `CountEnter`, `CountExit`) VALUES " +
		"(?,?,?,?,?,?,?)" +
		"ON DUPLICATE KEY UPDATE ZoneID=VALUES(ZoneID),DateTime=VALUES(DateTime),Date=VALUES(Date) ,DateHour=VALUES(DateHour) " +
		",DateMin=VALUES(DateMin) ,CountEnter=VALUES(CountEnter) ,CountExit=VALUES(CountExit);").Prepare()
	if err != nil {
		panic(err)
	}
	//var result int64 = 0
	for _, pf := range pfs {
		_, err := p.Exec(pf.ZoneId, pf.DateTime, pf.Date, pf.DateHour, pf.DateMin, pf.CountEnter, pf.CountExit)
		if err != nil {
			panic(err)
		}
		//n, err := res.RowsAffected()
		//if err != nil {
		//	panic(err)
		//}
		//result += n
	}
	p.Close()
	return len(pfs)
}

func InsertMulti(pfs []PassengerFlow) int64 {
	o := orm.NewOrm()
	nums, err := o.InsertMulti(5000, pfs)
	if err != nil {
		panic(err)
	}
	return nums
}
